#!/usr/bin/env python3
# -*- encoding: utf-8 -*-

import sqlite3
import mysql.connector
from sqlalchemy import INT, Column, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


def sqlExcete(conn):
    cursor = conn.cursor()
    try:
        cursor.execute("drop table if exists user")
        cursor.execute("create table user(id int(11) primary key, name varchar(20),\
                       password varchar(20))")
        cursor.execute("insert into user values (1, 'pengxj', '123456')")
        cursor.execute("insert into user values (2, 'pengxj2', '123456')")
        cursor.execute("select name from user")
        resultList = cursor.fetchall()
        nameList = []
        for name in resultList:
            nameList.append(name[0])
        print(nameList)
        conn.commit()
    except Exception as e:
        print("出现错误", e)
    finally:
        cursor.close()
        conn.close()


def sqlliteTest():
    conn = sqlite3.connect("test.db")
    sqlExcete(conn)


def mysqlTest():
    conn = mysql.connector.connect(
        user='root', password='root', database='base')
    sqlExcete(conn)


Base = declarative_base()
engine = create_engine('mysql+mysqlconnector://root:root@127.0.0.1:3306/base')
DBSession = sessionmaker(bind=engine)


class User(Base):
    # 绑定表名
    __tablename__ = 'user'
    # 表结构
    id = Column(INT, primary_key=True)
    name = Column(String(20))
    password = Column(String(20))


def sqlalchemyTest():
    session = DBSession()
    # user = User(id=5, name="pengxj3", password="123456")
    # session.add(user)
    # session.commit()
    userList = session.query(User).all()
    for user in userList:
        print("userId:%d, userName:%s, userPwd:%s" % (user.id, user.name, user.password))
    session.close()


# mysqlTest()
sqlalchemyTest()
